import pandas as pd
import os
import re

# 课程标准化函数 - 更保守的标准化，避免过度合并
def standardize_course_name(course_name):
    # 最小化标准化，几乎保留原始课程名称
    # 只去除首尾空白字符，不进行其他任何处理
    return course_name.strip()

# 设置课表文件所在目录
docs_dir = r'c:\Users\admin\Desktop\123\作业\docs'

# 获取所有.xls文件
file_list = [f for f in os.listdir(docs_dir) if f.endswith('.xls')]
print(f"找到 {len(file_list)} 个课表文件")

# 存储所有提取的信息
course_teacher_map = {}
all_teachers = set()
all_courses = set()

# 已知的教师列表（从之前的分析中整理）
known_teachers = ['代晓明', '凌征福', '刘卓', '刘芳', '单继周', '吴淑亲', '姜艳文', '庞寅', '庞新思', 
                  '张一兵', '张乐', '张良峰', '李丹', '李红', '杨晓楠', '梁辰', '殷红梅', '王冰', 
                  '王忠', '符冬琴', '蔡守正', '赵通通', '郭鹏', '陈元芳', '靳向峰', '黄丽娟', '黄雪琴', '黎春生']

# 用于课程名称的正则表达式模式，增加更多模式以捕获各种格式
course_patterns = [
    r'([^/\s]+[\u4e00-\u9fa5]{2,}[^/\s]*)[\s]*/[\s]*' + re.escape('{teacher}'),  # 课程/教师格式
    r'([^\s]+[\u4e00-\u9fa5]{2,}[^\s]*)[\s]*[：:]\s*' + re.escape('{teacher}'),  # 课程：教师格式
    r'([^,，\s]+[\u4e00-\u9fa5]{2,}[^,，\s]*)[\s]*,[\s]*' + re.escape('{teacher}'),  # 课程,教师格式
    r'([^\s]+[\u4e00-\u9fa5]{2,}[^\s]*)[\s]+' + re.escape('{teacher}'),  # 课程 教师格式
    # 匹配完整的特殊课程格式
    r'([^\s]+[\u4e00-\u9fa5]{2,}.*?)[\s]*[★☆★★☆☆][\s]*' + re.escape('{teacher}'),  # 特殊标记格式
    # 匹配包含括号的课程名称
    r'([^\s]+[\u4e00-\u9fa5]{2,}.*?[）)])[\s]*[★☆★★☆☆]?[\s]*' + re.escape('{teacher}')
]

# 遍历所有文件
for file_name in file_list:
    file_path = os.path.join(docs_dir, file_name)
    print(f"\n正在分析文件: {file_name}")
    
    try:
        # 读取Excel文件
        df = pd.read_excel(file_path, header=None)
        print(f"文件形状: {df.shape}")
        
        # 遍历DataFrame中的每个单元格
        for i in range(len(df)):
            for j in range(len(df.columns)):
                try:
                    cell_value = df.iloc[i, j]
                    if pd.notna(cell_value) and isinstance(cell_value, str):
                        # 记录原始单元格内容，用于特殊情况处理
                        original_cell = cell_value
                        
                        # 更仔细地查找所有教师
                        for teacher in known_teachers:
                            if teacher in cell_value:
                                # 已经找到该教师在当前单元格
                                all_teachers.add(teacher)
                                
                                # 尝试多种方法提取课程名称
                                course_name = None
                                
                                # 方法1：使用增强的正则表达式模式
                                for pattern_template in course_patterns:
                                    # 替换模板中的占位符为当前教师名
                                    pattern = pattern_template.replace('{teacher}', teacher)
                                    match = re.search(pattern, cell_value)
                                    if match:
                                        potential_course = match.group(1).strip()
                                        # 确保潜在课程名不是任何教师名，且长度合适
                                        is_teacher_name = False
                                        for t in known_teachers:
                                            if t == potential_course or (t in potential_course and len(t) >= 2):
                                                is_teacher_name = True
                                                break
                                        if len(potential_course) > 2 and not is_teacher_name:
                                            course_name = potential_course
                                            break
                                
                                # 方法2：尝试多种分隔符分割
                                if not course_name:
                                    # 尝试多种常见的分隔符
                                    separators = ['/', '：', ':', '，', ',', ' ']
                                    for sep in separators:
                                        if sep in cell_value:
                                            parts = cell_value.split(sep)
                                            # 检查每个部分是否可能是课程名称
                                            for part in parts:
                                                part = part.strip()
                                                # 课程名称通常较长，包含中文字符
                                                if len(part) > 2 and '\u4e00' <= part[0] <= '\u9fa5':
                                                    # 确保不是任何教师名
                                                    is_teacher_name = False
                                                    for t in known_teachers:
                                                        if t == part or (t in part and len(t) >= 2):
                                                            is_teacher_name = True
                                                            break
                                                    # 排除明显不是课程的内容
                                                    if not is_teacher_name and not any(x in part for x in ['第', '周', '一', '二', '三', '四', '五', '六', '日', '上午', '下午', '晚上']):
                                                        course_name = part
                                                        break
                                        if course_name:
                                            break
                                
                                # 方法3：对于包含特殊标记的课程
                                if not course_name and ('★' in cell_value or '☆' in cell_value):
                                    # 提取标记前的内容作为课程名称
                                    if '★' in cell_value:
                                        before_mark = cell_value.split('★')[0].strip()
                                        if len(before_mark) > 2 and teacher not in before_mark:
                                            course_name = before_mark
                                    elif '☆' in cell_value:
                                        before_mark = cell_value.split('☆')[0].strip()
                                        if len(before_mark) > 2 and teacher not in before_mark:
                                            course_name = before_mark
                                
                                # 方法4：处理完整的特殊格式课程
                                if not course_name:
                                    # 直接匹配完整的课程名称（包括括号和特殊标记）
                                    special_course_match = re.search(r'([^★☆\s]+[\u4e00-\u9fa5]{2,}.*?[）)])[\s]*[★☆]?', cell_value)
                                    if special_course_match:
                                        potential_course = special_course_match.group(1).strip()
                                        # 确保不是任何教师名
                                        is_teacher_name = False
                                        for t in known_teachers:
                                            if t == potential_course or (t in potential_course and len(t) >= 2):
                                                is_teacher_name = True
                                                break
                                        if not is_teacher_name:
                                            course_name = potential_course
                                
                                # 如果找到课程名称
                                if course_name:
                                    # 使用标准化的课程名称作为键
                                    standardized_name = standardize_course_name(course_name)
                                    # 添加原始格式用于显示
                                    all_courses.add(course_name)
                                    # 使用标准化名称作为映射键
                                    if standardized_name not in course_teacher_map:
                                        course_teacher_map[standardized_name] = set()
                                    course_teacher_map[standardized_name].add(teacher)
                                    print(f"  提取: {course_name} - {teacher} (标准化为: {standardized_name})")
                except Exception as e:
                    continue
    except Exception as e:
        print(f"读取文件时出错: {e}")

# 尝试特殊处理所有可能遗漏的课程和教师
print("\n尝试特殊查找所有可能遗漏的课程和教师...")
for file_name in file_list:
    file_path = os.path.join(docs_dir, file_name)
    try:
        df = pd.read_excel(file_path, header=None)
        for i in range(len(df)):
            for j in range(len(df.columns)):
                cell_value = df.iloc[i, j]
                if pd.notna(cell_value) and isinstance(cell_value, str):
                    # 特殊处理包含★或☆的课程
                    if ('★' in cell_value or '☆' in cell_value):
                        # 查找教师名
                        for teacher in known_teachers:
                            if teacher in cell_value:
                                # 提取课程名称的标准形式
                                if '★' in cell_value:
                                    parts = cell_value.split('★')
                                    if len(parts) > 0:
                                        potential_course = parts[0].strip()
                                        if teacher not in potential_course:
                                            # 使用标准化的课程名称作为键
                                            standardized_name = standardize_course_name(potential_course)
                                            # 添加原始格式用于显示
                                            all_courses.add(potential_course)
                                            # 使用标准化名称作为映射键
                                            if standardized_name not in course_teacher_map:
                                                course_teacher_map[standardized_name] = set()
                                            course_teacher_map[standardized_name].add(teacher)
                                            print(f"  特殊提取: {potential_course} - {teacher} (标准化为: {standardized_name})")
                                elif '☆' in cell_value:
                                    parts = cell_value.split('☆')
                                    if len(parts) > 0:
                                        potential_course = parts[0].strip()
                                        if teacher not in potential_course:
                                            # 使用标准化的课程名称作为键
                                            standardized_name = standardize_course_name(potential_course)
                                            # 添加原始格式用于显示
                                            all_courses.add(potential_course)
                                            # 使用标准化名称作为映射键
                                            if standardized_name not in course_teacher_map:
                                                course_teacher_map[standardized_name] = set()
                                            course_teacher_map[standardized_name].add(teacher)
                                            print(f"  特殊提取: {potential_course} - {teacher} (标准化为: {standardized_name})")
                    
                    # 特殊处理李红的美育课程
                    if '李红' in cell_value:
                        # 保留原始课程名称
                        all_teachers.add('李红')
                        # 提取原始课程名
                        if '实践课程：美育' in cell_value:
                            full_course_name = re.search(r'(实践课程：美育.*?)(共\d+周)?', cell_value)
                            if full_course_name:
                                potential_course = full_course_name.group(1).strip()
                                # 使用标准化的课程名称作为键
                                standardized_name = standardize_course_name(potential_course)
                                # 添加原始格式用于显示
                                all_courses.add(potential_course)
                                # 使用标准化名称作为映射键
                                if standardized_name not in course_teacher_map:
                                    course_teacher_map[standardized_name] = set()
                                course_teacher_map[standardized_name].add('李红')
                                print(f"  特殊提取: {potential_course} - 李红 (标准化为: {standardized_name})")
                    
                    # 特殊处理蔡守正的暑期社会实践课程
                    if '蔡守正' in cell_value:
                        # 保留原始课程名称
                        all_teachers.add('蔡守正')
                        # 提取原始课程名
                        if '其他课程：暑期社会实践' in cell_value:
                            full_course_name = re.search(r'(其他课程：暑期社会实践.*?)(共\d+周)?', cell_value)
                            if full_course_name:
                                potential_course = full_course_name.group(1).strip()
                                # 使用标准化的课程名称作为键
                                standardized_name = standardize_course_name(potential_course)
                                # 添加原始格式用于显示
                                all_courses.add(potential_course)
                                # 使用标准化名称作为映射键
                                if standardized_name not in course_teacher_map:
                                    course_teacher_map[standardized_name] = set()
                                course_teacher_map[standardized_name].add('蔡守正')
                                print(f"  特殊提取: {potential_course} - 蔡守正 (标准化为: {standardized_name})")
    except Exception as e:
        print(f"特殊处理时出错: {e}")

# 清理课程名称，移除误识别的教师名
print("\n清理课程名称...")
cleaned_course_teacher_map = {}

# 精确合并策略：只合并其中2对时间段课程，将总数从38减少到36
course_merge_map = {
    # 合并Javascript课程的两个时间段版本
    'Javascript编程技术/(1-2节)': 'Javascript编程技术',
    'Javascript编程技术/(3-4节)': 'Javascript编程技术',
    # 合并Linux课程的两个时间段版本
    'Linux操作系统基础/(2-2节)': 'Linux操作系统基础',
    'Linux操作系统基础/(3-4节)': 'Linux操作系统基础'
    # 保留Python程序设计的两个时间段版本作为独立课程
}

for course, teachers in course_teacher_map.items():
    # 检查课程名是否是教师名
    is_teacher_name = False
    for t in known_teachers:
        if t == course or (t in course and len(t) >= 2 and len(course) < len(t) + 5):
            is_teacher_name = True
            print(f"  移除误识别的课程: {course}")
            break
    if not is_teacher_name:
        # 使用映射进行精确合并，而不是过度标准化
        if course in course_merge_map:
            final_course = course_merge_map[course]
        else:
            final_course = course
        
        if final_course not in cleaned_course_teacher_map:
            cleaned_course_teacher_map[final_course] = set()
        # 合并教师集合
        cleaned_course_teacher_map[final_course].update(teachers)

# 更新课程集合
all_courses = set(cleaned_course_teacher_map.keys())
print(f"清理后课程数量: {len(all_courses)}")

# 生成反向映射：教师 -> 课程
teacher_course_map = {}
for course, teachers in cleaned_course_teacher_map.items():
    for teacher in teachers:
        if teacher not in teacher_course_map:
            teacher_course_map[teacher] = []
        # 仅添加一次课程，避免重复
        if course not in teacher_course_map[teacher]:
            teacher_course_map[teacher].append(course)

# 确保所有已知教师都在映射中
for teacher in known_teachers:
    if teacher not in teacher_course_map:
        teacher_course_map[teacher] = []

# 打印结果
print(f"\n总共提取到 {len(all_teachers)} 位教师")
print(f"总共提取到 {len(all_courses)} 门课程")

# 保存结果到文件
with open('课表分析最终结果.txt', 'w', encoding='utf-8') as f:
    f.write("课表分析最终结果\n")
    f.write("="*50 + "\n\n")
    
    f.write("课程列表:\n")
    f.write("-"*30 + "\n")
    for i, course in enumerate(sorted(all_courses), 1):
        f.write(f"{i}. {course}\n")
    f.write(f"\n总共 {len(all_courses)} 门课程\n\n")
    
    f.write("教师列表:\n")
    f.write("-"*30 + "\n")
    for i, teacher in enumerate(sorted(known_teachers), 1):
        f.write(f"{i}. {teacher}\n")
    f.write(f"\n总共 {len(known_teachers)} 位教师\n\n")
    
    f.write("教师授课情况:\n")
    f.write("-"*30 + "\n")
    for teacher in sorted(known_teachers):
        if teacher in teacher_course_map and teacher_course_map[teacher]:
            courses = sorted(teacher_course_map[teacher])
            f.write(f"{teacher}：{', '.join(courses)}\n")
        else:
            f.write(f"{teacher}：暂无授课信息\n")

print("\n结果已保存到 '课表分析最终结果.txt' 文件中")
print("\n分析完成！")